package com.uci.scheduleshare.calendardb;

import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.uci.scheduleshare.calendar.Contact;
import com.uci.scheduleshare.calendar.Event;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

/**
 * CS 190 Spring 2012
 * CalendarDatabase.java
 * A SQLite database for holding contact, event, and activity information
 * @author Patrick Hsu, Lauren Ko, Pomi Park
 *
 */
public class CalendarDatabase extends SQLiteOpenHelper {
	
	public static final String DB_NAME = "scheduleshare.db";
	public static final int DB_VERSION = 1;
	
	// Columns for Contact table
	private static final String CONTACT_TABLE_NAME = "contacts";
	private static final String CONTACT_ID = "_id";
	private static final String CONTACT_NAME = "name";
	private static final String CONTACT_CALENDARNAME = "calendarname";
	private static final String CONTACT_COLOR = "color";
	
	// Columns for Activities table
	private static final String ACTIVITY_TABLE_NAME = "activities";
	private static final String ACTIVITY_ID = "_id";
	private static final String ACTIVITY_DATE = "date";
	private static final String ACTIVITY_CONTENT = "content";
	
	// Columns for Events table
	private static final String EVENT_TABLE_NAME = "events";
	private static final String EVENT_ID = "_id";
	private static final String EVENT_NAME = "name";
	private static final String EVENT_CONTACT_ID = "contactid";
	private static final String EVENT_START_DAY = "startday";
	private static final String EVENT_START_MIN = "startmin";
	private static final String EVENT_END_DAY = "endday";
	private static final String EVENT_END_MIN = "endmin";
	
	private static final String[] CONTACT_PROJECTION = new String[] {
		CONTACT_ID, 
		CONTACT_NAME, 
		CONTACT_CALENDARNAME,
		CONTACT_COLOR
	};
	private static final String[] EVENT_PROJECTION = new String[] {
		EVENT_ID, 
		EVENT_NAME,
		EVENT_CONTACT_ID,
		EVENT_START_DAY,
		EVENT_START_MIN,
		EVENT_END_DAY,
		EVENT_END_MIN
	};
	
	private static final String[] ACTIVITY_PROJECTION = new String [] {
		ACTIVITY_DATE,
		ACTIVITY_CONTENT
	};
	
	private SQLiteDatabase calendardb;

	/**
	 * CalendarDatabase constructor - instantiate the database
	 */
	public CalendarDatabase(Context context, String name,
			CursorFactory factory, int version) {
		super(context, name, factory, version);
		calendardb = getWritableDatabase();
	}
	
	/**
	 * Create all the tables
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL("CREATE TABLE contacts (_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
					+ "name TEXT, calendarname TEXT, color INTEGER);");
		db.execSQL("CREATE TABLE events (_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ "name TEXT, contactid INTEGER, startday INTEGER, startmin INTEGER, endday INTEGER, endmin INTEGER);");
		db.execSQL("CREATE TABLE activities (_id INTEGER PRIMARY KEY AUTOINCREMENT, " 
				+ "date TEXT, content TEXT);");
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	}
	
	/**
	 * Insert a contact
	 * @param name
	 * @param calendarname
	 * @param color
	 * @return the contact ID as an int
	 */
	public int insertContact(String name, String calendarname, int color) {
		ContentValues cv = new ContentValues();
		cv.put(CONTACT_NAME,name);
		cv.put(CONTACT_CALENDARNAME, calendarname);
		cv.put(CONTACT_COLOR, color);
		int id = (int)calendardb.insert(CONTACT_TABLE_NAME, null, cv);
		return id;
	}
	
	/**
	 * Delete a contact
	 * @param id
	 * @return an int representing the rows updated
	 */
	public int deleteContact(int id) {
		int b = deleteEvents(id);
		int a = calendardb.delete(CONTACT_TABLE_NAME, CONTACT_ID + "=" + id, null);
		return a;
	}
	
	/**
	 * Get all the contacts in the database
	 * @return a list of all Contacts
	 */
	public List<Contact> getAllContacts() {
		List<Contact> contacts = new ArrayList<Contact>();
		Cursor c = calendardb.query(CONTACT_TABLE_NAME, CONTACT_PROJECTION, 
				null, null, null, null,null);

		if (c.moveToFirst()) {
			final int indexId = c.getColumnIndex(CONTACT_ID);
			final int indexName = c.getColumnIndex(CONTACT_NAME);
			final int indexCalendarname = c.getColumnIndex(CONTACT_CALENDARNAME);
			final int colorName = c.getColumnIndex(CONTACT_COLOR);
			do {
				int id = c.getInt(indexId);
				String name = c.getString(indexName);
				String calendarname = c.getString(indexCalendarname);
				List<Event> events = getEvents(id);
				int color = c.getInt(colorName);
				Contact contact = new Contact(name, calendarname, id, events, color, false);
				
				contacts.add(contact);
				
			} while (c.moveToNext());
		}
		c.close();
		return contacts;
	}
	
	/**
	 * Insert an event
	 * @param contactid
	 * @param events
	 */
	public void insertEvents(int contactid, List<Event> events) {
		ContentValues cv;
		for(Event e : events) {
			cv = new ContentValues();
			cv.put(EVENT_NAME, e.getEventName());
			cv.put(EVENT_CONTACT_ID, contactid);
			cv.put(EVENT_START_DAY, e.getStartDay());
			cv.put(EVENT_START_MIN, e.getStartMin());
			cv.put(EVENT_END_DAY, e.getEndDay());
			cv.put(EVENT_END_MIN, e.getEndMin());
			int id = (int)calendardb.insert(EVENT_TABLE_NAME, null, cv);
		}
	}
	
	/**
	 * Delete an event
	 * @param contactid
	 * @return an int representing the number of rows deleted
	 */
	public int deleteEvents(int contactid) {
		int a = calendardb.delete(EVENT_TABLE_NAME, EVENT_CONTACT_ID + "=" + contactid, null);
		return a;
	}
	
	/**
	 * Get all the events related to a contact id
	 * @param contactid
	 * @return a List of Events
	 */
	public List<Event> getEvents(int contactid) {
		List<Event> events = new ArrayList<Event>();
		String selection = "( " + EVENT_CONTACT_ID + " = ? )";
		String[] selectionArgs = new String[] { Integer.toString(contactid) };
		Cursor c = calendardb.query(EVENT_TABLE_NAME, EVENT_PROJECTION, selection, selectionArgs, null, null, null);
		if (c.moveToFirst()) {
			//final int indexId = c.getColumnIndex(EVENT_ID);
			final int indexName = c.getColumnIndex(EVENT_NAME);
			//final int indexContactid = c.getColumnIndex(EVENT_CONTACT_ID);
			final int indexStartday = c.getColumnIndex(EVENT_START_DAY);
			final int indexStartmin = c.getColumnIndex(EVENT_START_MIN);
			final int indexEndday = c.getColumnIndex(EVENT_END_DAY);
			final int indexEndmin = c.getColumnIndex(EVENT_END_MIN);

			do {
				String name = c.getString(indexName);
				int startday = c.getInt(indexStartday);
				int startmin = c.getInt(indexStartmin);
				int endday = c.getInt(indexEndday);
				int endmin = c.getInt(indexEndmin);
				
				events.add(new Event(name, startday, startmin, endday, endmin));
			} while (c.moveToNext());
		}
		c.close();
		return events;
	}
	
	/**
	 * Add a contact - insert into Contact table and insert all events into Event table
	 * @param contact
	 * @return the ID of the contact as an int
	 */
	public int addContact(Contact contact) {
		int id = insertContact(contact.getName(), contact.getCalendarName(), contact.getColor());
		insertEvents(id, contact.getEvents());
		return id;
	}
	
	/**
	 * Get all the recent activities of the user
	 * @return a List of strings of the activities
	 */
	public List<String> getRecentActivity() {
		List<String> activities = new ArrayList<String>();
		Cursor c = calendardb.query(ACTIVITY_TABLE_NAME, ACTIVITY_PROJECTION, null, null, null, null, null);
		if(c.moveToFirst()) {
			final int contentName = c.getColumnIndex(ACTIVITY_CONTENT);
			do {
				String content = c.getString(contentName);
				activities.add(content);
			} while(c.moveToNext());
		}
		c.close();
		return activities;
	}
	
	/**
	 * Add an activity
	 * @param username
	 * @param calendarname
	 * @param addOrDelete
	 * @return a string of the text added
	 */
	public String addActivity(String username, String calendarname, int addOrDelete) {
		DateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
		Date date = new Date();
		String text = "";
		if(addOrDelete == 0) {
			text = "Added " + username + "'s schedule \"" + calendarname + "\"";
		} else {
			text = "Deleted " + username + "'s schedule \"" + calendarname + "\"";
		}
		ContentValues cv = new ContentValues();
		cv.put(ACTIVITY_DATE, dateFormat.format(date));
		cv.put(ACTIVITY_CONTENT, text);
		int id = (int)calendardb.insert(ACTIVITY_TABLE_NAME, null, cv);
		return text;
	}
	
	/**
	 * Change the color of the contact 
	 * @param contactid
	 * @param color
	 */
	public void setColor(int contactid, int color) {
		ContentValues cv = new ContentValues();
		cv.put(CONTACT_COLOR, color);
		String where = CONTACT_ID + " = ?";
		String[] whereArgs = {Integer.toString(contactid)};
		int update = (int) calendardb.update(CONTACT_TABLE_NAME, cv, where, whereArgs);
	}
	
	/**
	 * Close the database
	 */
	public void close() {
		if(calendardb != null) {
			calendardb.close();
		}
	}
	
	

}
